package org.example.managementsystem.repository;

import org.example.managementsystem.model.Production_Submission;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Objects;
@Repository
public class Production_SubmissionDao {
    private final JdbcTemplate jdbcTemplate;
    @Autowired
    public Production_SubmissionDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int insertProduction_Submission(Production_Submission production_submission) {
        String sql = "INSERT INTO production_submission ( plan_id, description, is_approved,submitter_job_number) " +
                "VALUES (?, ?, ?, ?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setLong(1, production_submission.getPlan_id());
            ps.setString(2, production_submission.getDescription());
            ps.setBoolean(3, production_submission.isIs_approved());
            ps.setString(4, production_submission.getSubmitter_job_number());
            return ps;
        }, keyHolder);
        return Objects.requireNonNull(keyHolder.getKey()).intValue();
    }
    public int updateProduction_Submission(Production_Submission production_submission) {
        String sql = "UPDATE production_submission SET plan_id = ?, description = ?, is_approved = ?, submitter_job_number = ? " +
                "WHERE submission_id = ?";

        return jdbcTemplate.update(sql,
                production_submission.getPlan_id(),
                production_submission.getDescription(),
                production_submission.isIs_approved(),
                production_submission.getSubmitter_job_number(),
                production_submission.getSubmission_id()
                );

    }
    public Production_Submission getProduction_SubmissionById(int submission_id) {
        String sql = "SELECT * FROM production_submission WHERE submission_id = ?";
        List<Production_Submission> production_submissions = jdbcTemplate.query(sql, new Production_SubmissionRowMapper(), submission_id);
        return production_submissions.isEmpty() ? null : production_submissions.get(0);
    }
    public List<Production_Submission> getProduction_SubmissionByPlanId(Long plan_id) {
        String sql = "SELECT * FROM production_submission WHERE plan_id = ?";
        return jdbcTemplate.query(sql, new Production_SubmissionRowMapper(), plan_id);
    }
    public int deleteProduction_Submission(int submission_id) {
        String sql = "DELETE FROM production_submission WHERE submission_id = ?";
        return jdbcTemplate.update(sql, submission_id);
    }
public List<Production_Submission> getAllApplicationUNPASS(){
    String sql = "SELECT * FROM production_submission where is_approved = 0";
    return jdbcTemplate.query(sql, new Production_SubmissionRowMapper());
}
    public List<Production_Submission> getAllProduction_Submissions() {
        String sql = "SELECT * FROM production_submission";
        return jdbcTemplate.query(sql, new Production_SubmissionRowMapper());
    }
    private static class Production_SubmissionRowMapper implements RowMapper<Production_Submission> {
        @Override
        public Production_Submission mapRow(ResultSet rs, int rowNum) throws SQLException {
            Production_Submission production_submission = new Production_Submission();
            production_submission.setSubmission_id(rs.getInt("submission_id"));
            production_submission.setPlan_id(rs.getLong("plan_id"));
            production_submission.setIs_approved(rs.getBoolean("is_approved"));
            production_submission.setDescription(rs.getString("description"));
            production_submission.setSubmitter_job_number(rs.getString("submitter_job_number"));

            return production_submission;
        }
    }
}
